﻿CREATE FUNCTION [dbo].[WorkFlowFromByGroup](
	@StatusNameSpace sysname,
	@GroupName sysname
)RETURNS TABLE AS

RETURN(
WITH Menus(MenuID,MenuPath,WFIndex)AS(
	SELECT DISTINCT wf.MenuID, wf.MenuPath, wf.WFIndex
	FROM v_WorkFlow AS wf	INNER JOIN t_Workflow_Groups AS wfg
	ON wf.MenuID = wfg.MenuID AND
		 wf.NextMenuID = wfg.NextMenuID AND
		 wf.StatusNameSpace = @StatusNameSpace AND
		 GroupName = @GroupName
)
SELECT DISTINCT TOP 100 PERCENT wf.MenuID,wf.MenuPath,CONVERT(bit,ISNULL(Menus.MenuID,0)) HasAccess,wf.WFIndex
FROM v_WorkFlow wf LEFT OUTER JOIN Menus ON Menus.MenuID = wf.MenuID
WHERE wf.MenuID > 0 AND wf.StatusNameSpace = @StatusNameSpace AND wf.MenuID <> 25
ORDER BY wf.WFIndex
)

